const router = require('koa-router')()
//订单管理模块
//待分派-处理中-生产完成，待出库-出库请求待处理-已完成
router.prefix('/order')

//1.创建订单
router.post('/', async (ctx, next) => {
    let id = ctx.request.body.userId;
    let money = ctx.request.body.money;
    let detail = ctx.request.body.detail;
    let posttime = ctx.request.body.posttime;
    let deadline = ctx.request.body.deadline;
    let customerCompany = ctx.request.body.customerCompany;
    let sql = `select customer_id from customers where customer_company = '${customerCompany}'`;
    let res = await ctx.db.EXCUTE(sql, []);
    let customerId = res[0].customer_id;
    let sql2 = "insert into orders (order_directorid, order_detail, order_posttime, order_deadline, order_money, order_customerid) values('" + id + "','" + detail + "', '" + posttime + "', '" + deadline + "', '" + money + "', '" + customerId + "')";
    let result = await ctx.db.EXCUTE(sql2, []);
    if (result.affectedRows) {
        ctx.body = {
            "status": 200,
            "msg": '订单创建成功'
        }
    }
})

//2.修改订单
router.put('/', async (ctx, next) => {
    let id = ctx.request.body.orderId;
    let money = ctx.request.body.money;
    let detail = ctx.request.body.detail;
    let posttime = ctx.request.body.posttime;
    let deadline = ctx.request.body.deadline;
    let sql2 = `update orders set order_money = ${money}, order_detail = '${detail}',order_posttime = '${posttime}',order_deadline = '${deadline}' WHERE order_id = ${id}`;
    await ctx.db.EXCUTE(sql2, []);
    let sql = `select order_type from orders where order_id = '${id}'`;
    let res = await ctx.db.EXCUTE(sql, []);
    let type = res[0].order_type;
    if (type == "处理中") {
        let sql3 = `update produce set product_ordead = '${deadline}', product_ocdetail = '${detail}' WHERE product_orderid = ${id}`;
        await ctx.db.EXCUTE(sql3, []);
    }
    let obj = {
        msg: "编辑成功",
        status: 200
    }
    ctx.body = JSON.stringify(obj);
})

//3.删除订单
router.delete('/', async (ctx, next) => {
    let sql = `delete from orders where order_id=${ctx.request.body.id}`
    let results = await ctx.db.EXCUTE(sql, []);
    let sql2 = `delete from produce where product_orderid=${ctx.request.body.id}`
    await ctx.db.EXCUTE(sql2, []);
    if (results.affectedRows > 0) {
        let obj = {
            status: 200,
            msg: '删除成功'
        }
        ctx.body = JSON.stringify(obj);
    }
})

//4.查找订单
router.get('/', async (ctx, next) => {
    let plannerId = ctx.request.query.plannerId;
    let directorId = ctx.request.query.directorId;
    let customerId = ctx.request.query.customerId;
    let countSql;
    if (plannerId && directorId && customerId) {
        countSql = `select count(*) from orders where order_plannerid = ${plannerId} and order_directorid = ${directorId} and order_customerid = ${customerId}`;
    } else if (plannerId && directorId) {
        countSql = `select count(*) from orders where order_plannerid = ${plannerId} and order_directorid = ${directorId}`;
    } else if (plannerId && customerId) {
        countSql = `select count(*) from orders where order_plannerid = ${plannerId} and order_customerid = ${customerId}`;
    } else if (directorId && customerId) {
        countSql = `select count(*) from orders where order_directorid = ${directorId} and order_customerid = ${customerId}`;
    } else if (plannerId) {
        countSql = `select count(*) from orders where order_plannerid = ${plannerId}`;
    } else if (directorId) {
        countSql = `select count(*) from orders where order_directorid = ${directorId}`;
    } else if (customerId) {
        countSql = `select count(*) from orders where order_customerid = ${customerId}`;
    } else {
        countSql = "select count(*) from orders";
    }
    let countResults = await ctx.db.EXCUTE(countSql, []);
    let total = countResults[0]["count(*)"];
    let pageSize = Number(ctx.request.query.size)
    let page = ctx.request.query.page
    let startRow = total > 0 ? ((page - 1) * pageSize) : 0;
    //实现分页限制查找
    let limitSql;
    if (plannerId && directorId && customerId) {
        limitSql = `select * from orders where order_plannerid = ${plannerId} and order_directorid = ${directorId} and order_customerid = ${customerId} order by order_id desc limit ${startRow},${pageSize}`;
    } else if (plannerId && directorId) {
        limitSql = `select * from orders where order_plannerid = ${plannerId} and order_directorid = ${directorId} order by order_id desc limit ${startRow},${pageSize}`;
    } else if (plannerId && customerId) {
        limitSql = `select * from orders where order_plannerid = ${plannerId} and order_customerid = ${customerId} order by order_id desc limit ${startRow},${pageSize}`;
    } else if (directorId && customerId) {
        limitSql = `select * from orders where order_directorid = ${directorId} and order_customerid = ${customerId} order by order_id desc limit ${startRow},${pageSize}`;
    } else if (plannerId) {
        limitSql = `select * from orders where order_plannerid = ${plannerId} order by order_id desc limit ${startRow},${pageSize}`;
    } else if (directorId) {
        limitSql = `select * from orders where order_directorid = ${directorId} order by order_id desc limit ${startRow},${pageSize}`;
    } else if (customerId) {
        limitSql = `select * from orders where order_customerid = ${customerId} order by order_id desc limit ${startRow},${pageSize}`;
    } else {
        limitSql = `select * from orders order by order_id desc limit ${startRow},${pageSize}`;
    }
    let results = await ctx.db.EXCUTE(limitSql, []);
    for (let i = 0; i < results.length; i++) {
        let sql = `select user_name from users where user_id = ${results[i].order_directorid}`;
        let res = await ctx.db.EXCUTE(sql, []);
        let directorName = res[0].user_name;
        results[i].directorName = directorName;
        if (results[i].order_plannerid) {
            let sql2 = `select user_name from users where user_id = ${results[i].order_plannerid}`;
            let res2 = await ctx.db.EXCUTE(sql2, []);
            let plannerName = res2[0].user_name;
            results[i].plannerName = plannerName;
        }
        if (results[i].order_customerid) {
            let sql3 = `select customer_company from customers where customer_id = ${results[i].order_customerid}`;
            let res3 = await ctx.db.EXCUTE(sql3, []);
            let customerCompany = res3[0].customer_company;
            results[i].customerCompany = customerCompany;
        }
    }
    let obj = {
        length: total,
        results: results,
        status: 200
    }
    ctx.body = JSON.stringify(obj);
})

//5.委派订单--自动生成生产订单
router.post('/distribution', async (ctx, next) => {
    let id = ctx.request.body.orderId;
    let plannerId = ctx.request.body.plannerId;
    //委派订单，更改订单信息
    // let sql = `select user_id from users where user_id = '${plannerId}'`;
    // let result = await ctx.db.EXCUTE(sql, []);
    // let plannerId = result[0].user_id;
    let sql2 = `update orders set order_plannerid = ${plannerId},order_type = '处理中' WHERE order_id = ${id}`;
    await ctx.db.EXCUTE(sql2, []);
    //获取订单信息
    let sql4 = `select order_detail,order_deadline from orders where order_id = ${id}`;
    let res = await ctx.db.EXCUTE(sql4, []);
    let detail = res[0].order_detail;
    let deadline = res[0].order_deadline;
    //自动创建生产订单
    let sql3 = "insert into produce (product_ocdetail, product_orderid, product_planid, product_ordead) values('" + detail + "','" + id + "', '" + plannerId + "', '" + deadline + "')";
    await ctx.db.EXCUTE(sql3, []);
    //插入收入
    // let financetype = "收入";
    // let sql5 = `select order_money from orders where order_id = ${id}`;
    // let result5 = await ctx.db.EXCUTE(sql5, []);
    // let money = result5[0].order_money;
    // let sql6 = "insert into finance (finance_type, finance_money, finance_orderid) values('" + financetype + "','" + money + "', '" + id + "')";
    // await ctx.db.EXCUTE(sql6, []);
    let obj = {
        msg: "委派成功",
        status: 200
    }
    ctx.body = JSON.stringify(obj);
})

//8.产品提库请求
router.post('/outRequire', async (ctx, next) => {
    //将detail处理字符串 申请提库
    let id = ctx.request.body.orderId;
    let detail = ctx.request.body.detail;
    let kind = "产品出库";
    let sql = "insert into requires (require_kind, require_orid, require_detail) values('" + kind + "','" + id + "','" + detail + "')";
    let sql2 = `update orders set order_type = '出库请求待处理' WHERE order_id = ${id}`;
    await ctx.db.EXCUTE(sql, []);
    await ctx.db.EXCUTE(sql2, []);
    let obj = {
        msg: "出库请求成功",
        status: 200
    }
    ctx.body = JSON.stringify(obj);
})

//6.查询生产计划员的下拉列表
router.get('/planners', async (ctx, next) => {
    let sql = `select * from users where user_roleid = (select role_id from roles where role_desc = '生产计划员')`;
    let results = await ctx.db.EXCUTE(sql, []);
    if (results) {
        let obj = {
            status: 200,
            results: results
        }
        ctx.body = JSON.stringify(obj);
    }
})

//7.查询货物的下拉列表
router.get('/goods', async (ctx, next) => {
    let sql = `select * from goods where goods_type = 'product'`;
    let results = await ctx.db.EXCUTE(sql, []);
    if (results) {
        let obj = {
            status: 200,
            results: results
        }
        ctx.body = JSON.stringify(obj);
    }
})

//8.查询公司下拉列表
router.get('/company', async (ctx, next) => {
    let sql = `select * from customers`;
    let results = await ctx.db.EXCUTE(sql, []);
    if (results) {
        let obj = {
            status: 200,
            results: results
        }
        ctx.body = JSON.stringify(obj);
    }
})

//获取经理列表
router.get('/directors', async (ctx, next) => {
    let sql = `select * from users where user_roleid = (select role_id from roles where role_desc = '产品经理')`;
    let results = await ctx.db.EXCUTE(sql, []);
    if (results) {
        let obj = {
            status: 200,
            results: results
        }
        ctx.body = JSON.stringify(obj);
    }
})
module.exports = router
//1.创建订单
//2.修改订单
//3.删除订单
//4.查找订单
//5.委派订单

/*
bin：bin文件内的www文件是整个项目的开始文件，当npm start时其实就是运行的这个文件，一般情况不需要处理这个文件。
node_modules：存放各种依赖包。
public：存放各种静态文件，文件目录也可以随时改变。
routes：存放用于编写路由的文件。 
views：存放模板文件 
app.js：所谓的入口文件，外层的改动可以部分写在这个里面 	 
package.json：管理项目依赖和配置
*/